package com.iot.hmi.utils;

import com.iot.hmi.entity.*;
import org.sqlite.SQLiteException;

import java.sql.*;
import java.util.*;

public class SqliteHelper {
    private static volatile Connection connection;
    private static final String CREATE_IOT_DEVICE = "create table if not exists iot_device(id integer primary key, protocol_type varchar(32), protocol_host varchar(64), protocol_port integer)";
    private static final String CREATE_IOT_DEVICE_ITEM = "create table if not exists iot_device_item(id integer primary key, ref_device_key varchar(64), item_address varchar(64), item_type varchar(64), item_style varchar(32))";
    private static final String CREATE_IOT_ITEM_CONFIG = "create table if not exists iot_item_config(id integer primary key, ref_item_key varchar(64), shut_min float, shut_max float, wait_min float, wait_max float, load_min float, load_max float)";
    private static final String CREATE_IOT_ITEM_PROCESS = "create table if not exists iot_item_process(id integer primary key, ref_item_key varchar(64), process_name varchar(64), process_min float, process_max float, process_period float)";
    private static final String CREATE_DEVICE_INFO = "create table if not exists device_info(id integer primary key, device_id integer, collector_box_id varchar(64), device_name varchar(64), asset_id varchar(64), foreign key(device_id) references iot_device(id))";
    static {
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:iot_hmi.db");
            connection.setAutoCommit(true);
            // 初始化
            try (Statement statement = statement()) {
                statement.execute(CREATE_IOT_DEVICE);
                statement.execute(CREATE_IOT_DEVICE_ITEM);
                statement.execute(CREATE_IOT_ITEM_CONFIG);
                statement.execute(CREATE_IOT_ITEM_PROCESS);
                statement.execute(CREATE_DEVICE_INFO); // 新增
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
    }

    public static void deleteIotDevice(String host, Integer port) throws SQLException {
        final String sql = String.format(
                "delete from iot_device where protocol_host = '%s' and protocol_port = %d",
                host, port
        );
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static void deleteIotDeviceItem(String host, Integer port) throws SQLException {
        final String refDeviceKey = refDeviceKey(host, port);
        final String sql = String.format("delete from iot_device_item where ref_device_key = '%s'", refDeviceKey);
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static void deleteIotItemConfig(String host, Integer port, String address) throws SQLException {
        final String refItemKey = refItemKey(host, port, address);
        final String sql = String.format("delete from iot_item_config where ref_item_key = '%s'", refItemKey);
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static void deleteIotItemProcess(String host, Integer port, String address) throws SQLException {
        final String refItemKey = refItemKey(host, port, address);
        final String sql = String.format("delete from iot_item_process where ref_item_key = '%s'", refItemKey);
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static void insertIotDevice(IotDevice device) throws SQLException {
        final String sql = String.format(
                "insert into iot_device(protocol_type,protocol_host,protocol_port) values('%s','%s',%d)",
                device.getProtocolType(), device.getProtocolHost(), device.getProtocolPort()
        );
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static void insertIotItemValue(String host, Integer port, String address, Double value) throws SQLException {
        final String tableName = refValueTableName(host, port, address);
        final String createSql = String.format(
                "create table if not exists %s(id integer primary key, item_value double, value_stamp bigint)",
                tableName
        );
        final String insertSql = String.format(
                "insert into %s(item_value,value_stamp) values(%f,%d)",
                tableName, value, System.currentTimeMillis()
        );
        try (Statement statement = statement()) {
            statement.execute(createSql);
            statement.execute(insertSql);
        }
    }

    public static void insertIotDeviceItem(IotDeviceItem item) throws SQLException {
        final String sql = String.format(
                "insert into iot_device_item(ref_device_key,item_address,item_type,item_style) values('%s','%s','%s','%s')",
                item.getRefDeviceKey(), item.getItemAddress(), item.getItemType(),
                item.getItemStyle() == null ? "" : item.getItemStyle()
        );
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static void insertIotItemConfig(IotItemConfig config) throws SQLException {
        final String sql = String.format(
                "insert into iot_item_config(ref_item_key,shut_min,shut_max,wait_min,wait_max,load_min,load_max) values('%s',%f,%f,%f,%f,%f,%f)",
                config.getRefItemKey(), config.getShutMin(), config.getShutMax(),
                config.getWaitMin(), config.getWaitMax(), config.getLoadMin(), config.getLoadMax()
        );
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }


    public static IotItemConfig queryIotItemConfig(String host, Integer port, String address) throws SQLException {
        final String sql = String.format(
                "select id,ref_item_key,shut_min,shut_max,wait_min,wait_max,load_min,load_max from iot_item_config where ref_item_key = '%s'",
                refItemKey(host, port, address)
        );
        try (Statement statement = statement()) {
            final ResultSet result = statement.executeQuery(sql);
            if (!result.next()) {
                return null;
            }
            IotItemConfig config = new IotItemConfig();
            config.setId(result.getInt(1));
            config.setRefItemKey(result.getString(2));
            config.setShutMin(result.getFloat(3));
            config.setShutMax(result.getFloat(4));
            config.setWaitMin(result.getFloat(5));
            config.setWaitMax(result.getFloat(6));
            config.setLoadMin(result.getFloat(7));
            config.setLoadMax(result.getFloat(8));
            return config;
        }
    }


    public static List<IotItemValue> listIotItemValue(String host, Integer port, String address, Long stampStart, Long stampEnd) throws SQLException {
        final String tableName = refValueTableName(host, port, address);
        final String sql = String.format(
                "select id,item_value,value_stamp from %s where value_stamp > %d and value_stamp < %d",
                tableName, stampStart, stampEnd
        );
        try (Statement statement = statement()) {
            List<IotItemValue> values = new LinkedList<>();
            final ResultSet result = statement.executeQuery(sql);
            while (result.next()) {
                IotItemValue value = new IotItemValue();
                value.setId(result.getInt(1));
                value.setItemValue(result.getDouble(2));
                value.setValueStamp(result.getLong(3));
                values.add(value);
            }
            return values;
        }catch (SQLiteException e){
            return new ArrayList<>(0);
        }
    }

    public static IotItemValue queryLatestValue(String host, Integer port, String address) throws SQLException {
        final String tableName = refValueTableName(host, port, address);
        final String sql = String.format(
                "select id,item_value,value_stamp from %s where id = (select max(id) from %s)",
                tableName, tableName
        );
        try (Statement statement = statement()) {
            final ResultSet result = statement.executeQuery(sql);
            if (!result.next()) {
                return null;
            }
            IotItemValue value = new IotItemValue();
            value.setId(result.getInt(1));
            value.setItemValue(result.getDouble(2));
            value.setValueStamp(result.getLong(3));
            return value;
        }
    }

    public static IotDevice queryIotDevice(String host, Integer port) throws SQLException {
        final String sql = String.format("select id,protocol_type,protocol_host,protocol_port from iot_device where protocol_host = '%s' and protocol_port = %d limit 1", host, port);
        try (Statement statement = statement()) {
            final ResultSet result = statement.executeQuery(sql);
            if (!result.next()) {
                return null;
            }
            IotDevice device = new IotDevice();
            device.setId(result.getInt(1));
            device.setProtocolType(result.getString(2));
            device.setProtocolHost(result.getString(3));
            device.setProtocolPort(result.getInt(4));
            return device;
        }
    }

    public static List<IotDeviceItem> listIotDeviceItem(String host, Integer port) throws SQLException {
        final String refDeviceKey = refDeviceKey(host, port);
        final String sql = String.format("select id,ref_device_key,item_address,item_type,item_style from iot_device_item where ref_device_key = '%s'", refDeviceKey);
        List<IotDeviceItem> items = new LinkedList<>();
        try (Statement statement = statement()) {
            final ResultSet result = statement.executeQuery(sql);
            while (result.next()) {
                IotDeviceItem item = new IotDeviceItem();
                item.setId(result.getInt(1));
                item.setRefDeviceKey(result.getString(2));
                item.setItemAddress(result.getString(3));
                item.setItemType(result.getString(4));
                item.setItemStyle(result.getString(5));
                items.add(item);
            }
        }
        return items;
    }

    // 新增插入设备信息的方法
    public static void insertDeviceInfo(int deviceId, String collectorBoxId, String deviceName, String assetId) throws SQLException {
        final String sql = String.format(
                "insert into device_info(device_id, collector_box_id, device_name, asset_id) values(%d, '%s', '%s', '%s')",
                deviceId, collectorBoxId, deviceName, assetId
        );
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    // 新增查询设备信息的方法
    public static DeviceInfo queryDeviceInfo(int deviceId) throws SQLException {
        final String sql = String.format("select id, device_id, collector_box_id, device_name, asset_id from device_info where device_id = %d", deviceId);
        try (Statement statement = statement()) {
            final ResultSet result = statement.executeQuery(sql);
            if (!result.next()) {
                return null;
            }
            DeviceInfo deviceInfo = new DeviceInfo();
            deviceInfo.setId(result.getInt(1));
            deviceInfo.setDeviceId(result.getInt(2));
            deviceInfo.setCollectorBoxId(result.getString(3));
            deviceInfo.setDeviceName(result.getString(4));
            deviceInfo.setAssetId(result.getString(5));
            return deviceInfo;
        }
    }
    // 新增更新设备信息的方法
    public static void updateDeviceInfo(int id, int deviceId, String collectorBoxId, String deviceName, String assetId) throws SQLException {
        final String sql = String.format(
                "update device_info set device_id = %d, collector_box_id = '%s', device_name = '%s', asset_id = '%s' where id = %d",
                deviceId, collectorBoxId, deviceName, assetId, id
        );
        try (Statement statement = statement()) {
            statement.execute(sql);
        }
    }

    public static String refDeviceKey(String host, Integer port) {
        return host + "_" + port;
    }

    public static String refItemKey(String host, Integer port, String address) {
        return host + "_" + port + "_" + address;
    }

    public static String refValueTableName(String host, Integer port, String address) {
        return "val_" + ip2int(host) + "_" + port + "_" + address.replaceAll("\\.", "_");
    }

    private static Statement statement() throws SQLException {
        return connection.createStatement();
    }

    public static int ip2int(String host) {
        final String[] ips = host.split("\\.");
        int result = 0;
        result |= Integer.parseInt(ips[0]) << 24;
        result |= Integer.parseInt(ips[1]) << 16;
        result |= Integer.parseInt(ips[2]) << 8;
        result |= Integer.parseInt(ips[3]);
        return result;
    }

    public static String int2ip(int ip) {
        return (ip >>> 24) + "." + ((ip & 0x00FFFFFF) >>> 16) + "." + ((ip & 0x0000FFFF) >>> 8) + "." + (ip & 0x000000FF);
    }

}
